package com.xb.system.mapper.db0;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xb.model.domain.Users;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * (Users)表数据库访问层
 *
 * @author makejava
 * @since 2024-07-15 13:18:59
 */
@Mapper
public interface UsersMapper extends BaseMapper<Users> {
    /**
     * 验证规则，手机号完全匹配，姓名包含匹配（因为数据库存储的是完整姓名）
     * @param phone
     * @param username
     * @return
     */
    @Select("SELECT COUNT(*) FROM users WHERE phone = #{phone} AND name LIKE CONCAT('%', #{username}, '%')")
    int checkUserMatch(@Param("phone") String phone, @Param("username") String username);
    
    /**
     * 调试方法：查看实际的查询SQL和结果
     */
    @Select("SELECT phone, name FROM users WHERE phone = #{phone} LIMIT 5")
    List<Map<String, Object>> debugQuery(@Param("phone") String phone);
    
    @Select("SELECT phone, name FROM users WHERE phone = #{phone} AND (name LIKE CONCAT('%', #{username}, '%') OR #{username} LIKE CONCAT('%', name, '%')) LIMIT 5")
    List<Map<String, Object>> checkUserMatchWithIndex(@Param("phone") String phone, @Param("username") String username);
    
    /**
     * 直接查询所有分片表，不使用分片逻辑
     * 利用普通索引进行快速匹配
     */
    @Select({
        "<script>",
        "SELECT phone, name FROM (",
        "<foreach collection='tableNames' item='tableName' separator=' UNION ALL '>",
        "SELECT phone, name FROM ${tableName} WHERE phone = #{phone}",
        "</foreach>",
        ") AS combined_result",
        "WHERE (name LIKE CONCAT('%', #{username}, '%') OR #{username} LIKE CONCAT('%', name, '%'))",
        "LIMIT 5",
        "</script>"
    })
    List<Map<String, Object>> checkUserMatchAllTables(@Param("phone") String phone, @Param("username") String username, @Param("tableNames") List<String> tableNames);
    
    /**
     * 机制一：单个表查询优化
     * 使用复合索引 idx_phone_name 进行快速匹配
     */
    @Select("SELECT phone, name FROM ${tableName} WHERE phone = #{phone} AND (name LIKE CONCAT('%', #{username}, '%') OR #{username} LIKE CONCAT('%', name, '%')) LIMIT 1")
    List<Map<String, Object>> checkUserMatchSingleTable(@Param("phone") String phone, @Param("username") String username, @Param("tableName") String tableName);
    
    /**
     * 调试方法：查看姓名匹配情况
     */
    @Select("SELECT phone, name FROM users WHERE name LIKE CONCAT('%', #{username}, '%') LIMIT 5")
    List<Map<String, Object>> debugNameMatch(@Param("username") String username);


    /**
     * 筛选匹配方法
     * 根据企业名称、法人姓名、电话号码进行筛选
     * 注意：由于表中只有name和phone字段，companyName参数暂时不使用
     */
    @Select({
        "<script>",
        "SELECT phone, name FROM ${tableName}",
        "WHERE 1=1",
        "<if test='legalRepresentative != null and legalRepresentative != \"\"'>",
        "AND (name LIKE CONCAT('%', #{legalRepresentative}, '%') OR #{legalRepresentative} LIKE CONCAT('%', name, '%'))",
        "</if>",
        "<if test='phone != null and phone != \"\"'>",
        "AND phone = #{phone}",
        "</if>",
        "LIMIT 1",
        "</script>"
    })
    List<Map<String, Object>> filterMatch(
        @Param("companyName") String companyName,
        @Param("legalRepresentative") String legalRepresentative,
        @Param("phone") String phone,
        @Param("tableName") String tableName
    );

    void createIndexes();
    
    void dropIndexes();

    long totalRows(@Param("index") int index);
}

